﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class AssetCptlInfoDao
    {
        public bool Delete(SqlConnection conn, int cptlId)
        {
            string commandText = "DELETE FROM [dbo].[Asset_DpctSub] WHERE [CptlId] = @CptlId DELETE FROM [dbo].[Asset_CptlInfo] WHERE [CptlId] = @CptlId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@CptlId", SqlDbType.Int) };
            commandParameters[0].Value = cptlId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, AssetCptlInfoData data)
        {
            string commandText = "INSERT INTO [dbo].[Asset_CptlInfo] ( [CptlNo], [CptlName], [TypeId], [DeptId], [CptlKind], [PrcsId], [CptlVal], [CptlBal], [DpctYY], [SumDect], [MonDect], [FinishFlag], [CreateDate], [DcrDate], [FromYYMM], [DcrPrcsId], [Keeper], [Remark] ) VALUES ( @CptlNo, @CptlName, @TypeId, @DeptId, @CptlKind, @PrcsId, @CptlVal, @CptlBal, @DpctYY, @SumDect, @MonDect, @FinishFlag, @CreateDate, @DcrDate, @FromYYMM, @DcrPrcsId, @Keeper, @Remark ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[0x12];
            commandParameters[0] = new SqlParameter("@CptlNo", SqlDbType.NVarChar, 100);
            commandParameters[0].Value = data.CptlNo;
            commandParameters[1] = new SqlParameter("@CptlName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.CptlName;
            commandParameters[2] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[2].Value = data.TypeId;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[3].Value = data.DeptId;
            commandParameters[4] = new SqlParameter("@CptlKind", SqlDbType.NVarChar, 10);
            commandParameters[4].Value = data.CptlKind;
            commandParameters[5] = new SqlParameter("@PrcsId", SqlDbType.Int);
            commandParameters[5].Value = data.PrcsId;
            commandParameters[6] = new SqlParameter("@CptlVal", SqlDbType.Decimal);
            commandParameters[6].Precision = 0x12;
            commandParameters[6].Scale = 2;
            commandParameters[6].Value = data.CptlVal;
            commandParameters[7] = new SqlParameter("@CptlBal", SqlDbType.Decimal);
            commandParameters[7].Precision = 0x12;
            commandParameters[7].Scale = 2;
            commandParameters[7].Value = data.CptlBal;
            commandParameters[8] = new SqlParameter("@DpctYY", SqlDbType.Decimal);
            commandParameters[8].Precision = 10;
            commandParameters[8].Scale = 2;
            commandParameters[8].Value = data.DpctYY;
            commandParameters[9] = new SqlParameter("@SumDect", SqlDbType.Decimal);
            commandParameters[9].Precision = 0x12;
            commandParameters[9].Scale = 2;
            commandParameters[9].Value = data.SumDect;
            commandParameters[10] = new SqlParameter("@MonDect", SqlDbType.Decimal);
            commandParameters[10].Precision = 0x12;
            commandParameters[10].Scale = 2;
            commandParameters[10].Value = data.MonDect;
            commandParameters[11] = new SqlParameter("@FinishFlag", SqlDbType.Int);
            commandParameters[11].Value = data.FinishFlag;
            commandParameters[12] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            commandParameters[12].Value = data.CreateDate;
            commandParameters[13] = new SqlParameter("@DcrDate", SqlDbType.DateTime);
            if (data.DcrDateIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.DcrDate;
            }
            commandParameters[14] = new SqlParameter("@FromYYMM", SqlDbType.DateTime);
            commandParameters[14].Value = data.FromYYMM;
            commandParameters[15] = new SqlParameter("@DcrPrcsId", SqlDbType.Int);
            if (data.DcrPrcsIdIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.DcrPrcsId;
            }
            commandParameters[0x10] = new SqlParameter("@Keeper", SqlDbType.NVarChar, 20);
            commandParameters[0x10].Value = data.Keeper;
            commandParameters[0x11] = new SqlParameter("@Remark", SqlDbType.NText);
            commandParameters[0x11].Value = data.Remark;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByCptlId(SqlConnection conn, int cptlId)
        {
            string commandText = "SELECT [Asset_CptlInfo].*, dbo.GetDeptNames([Asset_CptlInfo].[DeptId]) AS DeptName FROM [dbo].[Asset_CptlInfo] LEFT OUTER JOIN [dbo].[Asset_PrcsProp] ON [Asset_CptlInfo].[DcrPrcsId] = [Asset_PrcsProp].[PrcsId] WHERE [Asset_CptlInfo].[CptlId]= @CptlId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@CptlId", SqlDbType.Int) };
            commandParameters[0].Value = cptlId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Asset_CptlInfo";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            string commandText = "IF(@docount=1) SELECT COUNT([CptlId]) FROM [dbo].[Asset_CptlInfo] ELSE BEGIN DECLARE @IndexTable TABLE(ID int identity(1,1),PK int) DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound=(@pageindex-1)*@pagesize SET @PageUpperBound=@PageLowerBound+@pagesize SET rowcount @PageUpperBound INSERT INTO @IndexTable(PK) SELECT [CptlId] FROM [dbo].[Asset_CptlInfo] ORDER BY [CptlId] DESC SELECT [Asset_CptlInfo].*, [Asset_PrcsProp].[PrcsLongDesc], dbo.GetDeptNames([Asset_CptlInfo].[DeptId]) AS DeptName, @pageindex AS pageindex FROM [dbo].[Asset_CptlInfo], [dbo].[Asset_PrcsProp], @IndexTable t WHERE [Asset_CptlInfo].[PrcsId] = [Asset_PrcsProp].[PrcsId] AND [Asset_CptlInfo].[CptlId] = t.PK AND t.ID>@PageLowerBound AND t.ID<=@PageUpperBound END";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Asset_CptlInfo";
            return set.Tables[0];
        }

        public bool SetDcr(SqlConnection conn, int cptlId, DateTime dcrDate, int dcrPrcsId)
        {
            string commandText = "UPDATE [dbo].[Asset_CptlInfo] SET [DcrDate] = @DcrDate, [DcrPrcsId] = @DcrPrcsId WHERE [CptlId] = @CptlId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@DcrDate", SqlDbType.DateTime);
            commandParameters[0].Value = dcrDate;
            commandParameters[1] = new SqlParameter("@DcrPrcsId", SqlDbType.Int);
            commandParameters[1].Value = dcrPrcsId;
            commandParameters[2] = new SqlParameter("@CptlId", SqlDbType.Int);
            commandParameters[2].Value = cptlId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool Update(SqlConnection conn, AssetCptlInfoData data)
        {
            string commandText = "UPDATE [dbo].[Asset_CptlInfo] SET [CptlNo] = @CptlNo, [CptlName] = @CptlName, [TypeId] = @TypeId, [DeptId] = @DeptId, [CptlKind] = @CptlKind, [PrcsId] = @PrcsId, [CptlVal] = @CptlVal, [CptlBal] = @CptlBal, [DpctYY] = @DpctYY, [SumDect] = @SumDect, [MonDect] = @MonDect, [FinishFlag] = @FinishFlag, [CreateDate] = @CreateDate, [FromYYMM] = @FromYYMM, [Keeper] = @Keeper, [Remark] = @Remark WHERE [CptlId] = @CptlId";
            SqlParameter[] commandParameters = new SqlParameter[0x13];
            commandParameters[0] = new SqlParameter("@CptlId", SqlDbType.Int);
            commandParameters[0].Value = data.CptlId;
            commandParameters[1] = new SqlParameter("@CptlNo", SqlDbType.NVarChar, 100);
            commandParameters[1].Value = data.CptlNo;
            commandParameters[2] = new SqlParameter("@CptlName", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.CptlName;
            commandParameters[3] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[3].Value = data.TypeId;
            commandParameters[4] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[4].Value = data.DeptId;
            commandParameters[5] = new SqlParameter("@CptlKind", SqlDbType.NVarChar, 10);
            commandParameters[5].Value = data.CptlKind;
            commandParameters[6] = new SqlParameter("@PrcsId", SqlDbType.Int);
            commandParameters[6].Value = data.PrcsId;
            commandParameters[7] = new SqlParameter("@CptlVal", SqlDbType.Decimal);
            commandParameters[7].Precision = 0x12;
            commandParameters[7].Scale = 2;
            commandParameters[7].Value = data.CptlVal;
            commandParameters[8] = new SqlParameter("@CptlBal", SqlDbType.Decimal);
            commandParameters[8].Precision = 0x12;
            commandParameters[8].Scale = 2;
            commandParameters[8].Value = data.CptlBal;
            commandParameters[9] = new SqlParameter("@DpctYY", SqlDbType.Decimal);
            commandParameters[9].Precision = 10;
            commandParameters[9].Scale = 2;
            commandParameters[9].Value = data.DpctYY;
            commandParameters[10] = new SqlParameter("@SumDect", SqlDbType.Decimal);
            commandParameters[10].Precision = 0x12;
            commandParameters[10].Scale = 2;
            commandParameters[10].Value = data.SumDect;
            commandParameters[11] = new SqlParameter("@MonDect", SqlDbType.Decimal);
            commandParameters[11].Precision = 0x12;
            commandParameters[11].Scale = 2;
            commandParameters[11].Value = data.MonDect;
            commandParameters[12] = new SqlParameter("@FinishFlag", SqlDbType.Int);
            commandParameters[12].Value = data.FinishFlag;
            commandParameters[13] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            commandParameters[13].Value = data.CreateDate;
            commandParameters[14] = new SqlParameter("@DcrDate", SqlDbType.DateTime);
            if (data.DcrDateIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.DcrDate;
            }
            commandParameters[15] = new SqlParameter("@FromYYMM", SqlDbType.DateTime);
            commandParameters[15].Value = data.FromYYMM;
            commandParameters[0x10] = new SqlParameter("@DcrPrcsId", SqlDbType.Int);
            if (data.DcrPrcsIdIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.DcrPrcsId;
            }
            commandParameters[0x11] = new SqlParameter("@Keeper", SqlDbType.NVarChar, 20);
            commandParameters[0x11].Value = data.Keeper;
            commandParameters[0x12] = new SqlParameter("@Remark", SqlDbType.NText);
            commandParameters[0x12].Value = data.Remark;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

